package com.ph.irp.base.salespricepolicy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.salespricepolicy.dao.PBaseSalesPricePolicyDAO;
import com.ph.irp.model.PBaseSalespricepolicy;
import com.ph.util.DBUtil;
import com.ph.util.DateUtil;

public class PBaseSalesPricePolicyDAOImpl implements PBaseSalesPricePolicyDAO {

	/*
	 * private ComboPooledDataSource dataSource;
	 * 
	 * public ComboPooledDataSource getDataSource() { return dataSource; }
	 * 
	 * public void setDataSource(ComboPooledDataSource dataSource) {
	 * this.dataSource = dataSource; }
	 */

	private ComboPooledDataSource dataSource;

	private List<PBaseSalespricepolicy> salespricePolicyList;

	private PBaseSalespricepolicy salespricepolicyModel = null;

	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseSalespricepolicy> getSalespricePolicyList() {
		return salespricePolicyList;
	}

	public void setSalespricePolicyList(
			List<PBaseSalespricepolicy> salespricePolicyList) {
		this.salespricePolicyList = salespricePolicyList;
	}

	public PBaseSalespricepolicy getSalespricepolicyModel() {
		return salespricepolicyModel;
	}

	public void setSalespricepolicyModel(
			PBaseSalespricepolicy salespricepolicyModel) {
		this.salespricepolicyModel = salespricepolicyModel;
	}

	/*
	 * 查询销售价格政策记录
	 */
	@Override
	public List<PBaseSalespricepolicy> getAllSalesPricePolicyList()
			throws Throwable {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("select * from p_base_salespricepolicy");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());
			resultSet = preparedStatement.executeQuery();

			salespricePolicyList = this.turnResultSetToList(resultSet);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespricePolicyList;
	}

	/*
	 * 按照销售价格政策ID查询
	 */
	@Override
	public PBaseSalespricepolicy getSalespricepolicyById(
			Integer salesPricePolicyId) throws Throwable {
		// TODO Auto-generated method stub

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("select * from p_base_salespricepolicy where salesPricePolicyId =?");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setInt(1, salesPricePolicyId);

			resultSet = preparedStatement.executeQuery();

			salespricePolicyList = this.turnResultSetToList(resultSet);

			salespricepolicyModel = salespricePolicyList.get(0);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespricepolicyModel;
	}

	/*
	 * 按照销售价格政策代码查询
	 */
	@Override
	public List<PBaseSalespricepolicy> getSalespricePolicyByCode(
			String salesPricePolicyCode) throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("select * from p_base_salespricepolicy where salesPricePolicyCode like ?");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setString(1, "%" + salesPricePolicyCode + "%");

			resultSet = preparedStatement.executeQuery();

			salespricePolicyList = this.turnResultSetToList(resultSet);

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespricePolicyList;
	}

	/*
	 * 按照销售价格政策名称查询
	 */@Override
	public List<PBaseSalespricepolicy> getSalespricePolicyByName(
			String salesPricePolicyName) throws Throwable {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("select * from p_base_salespricepolicy where salesPricePolicyName like ?");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setString(1, "%" + salesPricePolicyName + "%");

			resultSet = preparedStatement.executeQuery();

			salespricePolicyList = this.turnResultSetToList(resultSet);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespricePolicyList;
	}

	/*
	 * 按照销售价格政策名称和代码一起查询
	 */
	@Override
	public List<PBaseSalespricepolicy> getSalespricePolicyByCodeAndName(
			String salesPricePolicyCode, String salesPricePolicyName)
			throws Throwable {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("select * from p_base_salespricepolicy where salesPricePolicyCode like ? and salesPricePolicyName like ?");

			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setString(1, "%" + salesPricePolicyCode + "%");
			preparedStatement.setString(2, "%" + salesPricePolicyName + "%");

			resultSet = preparedStatement.executeQuery();

			salespricePolicyList = this.turnResultSetToList(resultSet);
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return salespricePolicyList;
	}

	/*
	 * 添加销售价格政策记录
	 */
	@Override
	public void addSalespricePolicy(PBaseSalespricepolicy salespricepolicyModel)
			throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("insert into p_base_salespricepolicy ")
					.append(
							"(salesPricePolicyCode,salesPricePolicyName,validDateFrom,validDateTo,prior,compoundType)")
					.append("values (?,?,?,?,?,?)");

			connection = dataSource.getConnection();

			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setString(1, salespricepolicyModel
					.getSalesPricePolicyCode());
			preparedStatement.setString(2, salespricepolicyModel
					.getSalesPricePolicyName());
			preparedStatement.setString(3, salespricepolicyModel.getValidDateFrom());
			preparedStatement.setString(4, salespricepolicyModel.getValidDateTo());
			preparedStatement.setInt(5, salespricepolicyModel.getPrior());
			preparedStatement.setString(6, salespricepolicyModel
					.getCompoundType());

			preparedStatement.executeUpdate();

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/*
	 * 修改选中记录
	 */
	@Override
	public void updateSalespricePolicy(
			PBaseSalespricepolicy salespricepolicyModel) throws Throwable {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append(
							"update p_base_salespricepolicy set salesPricePolicyCode=?,salesPricePolicyName=?,")
					.append(
							"validDateFrom=?,validDateTo=?,prior=?,compoundType=? where salesPricePolicyId=?");

			connection = dataSource.getConnection();

			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setString(1, salespricepolicyModel
					.getSalesPricePolicyCode());
			preparedStatement.setString(2, salespricepolicyModel
					.getSalesPricePolicyName());
			preparedStatement.setString(3, salespricepolicyModel.getValidDateFrom());
			preparedStatement.setString(4, salespricepolicyModel.getValidDateTo());
			preparedStatement.setInt(5, salespricepolicyModel.getPrior());
			preparedStatement.setString(6, salespricepolicyModel
					.getCompoundType());
			preparedStatement.setInt(7, salespricepolicyModel
					.getSalesPricePolicyId());

			preparedStatement.executeUpdate();

		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

	}

	/*
	 * 删除选中记录
	 */
	@Override
	public void deleteSalespricePolicy(Integer salesPricePolicyId)
			throws Throwable {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {

			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder
					.append("delete from  p_base_salespricepolicy  where salesPricePolicyId=?");
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(stringBuilder
					.toString());

			preparedStatement.setInt(1, salesPricePolicyId);

			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(connection, preparedStatement, resultSet);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	/*
	 * 将结果集resultSet封装返回list对象
	 */
	public List<PBaseSalespricepolicy> turnResultSetToList(ResultSet resultSet)
			throws Throwable {
		salespricePolicyList = new ArrayList<PBaseSalespricepolicy>();

		while (resultSet.next()) {
			salespricepolicyModel = new PBaseSalespricepolicy();

			salespricepolicyModel.setSalesPricePolicyId(resultSet
					.getInt("salesPricePolicyId"));
			salespricepolicyModel.setSalesPricePolicyCode(resultSet
					.getString("salesPricePolicyCode"));
			salespricepolicyModel.setSalesPricePolicyName(resultSet
					.getString("salesPricePolicyName"));
			salespricepolicyModel.setValidDateFrom(DateUtil.date2String(resultSet
					.getDate("validDateFrom"), "yyyy-MM-dd"));
			salespricepolicyModel.setValidDateTo(DateUtil.date2String(resultSet
					.getDate("validDateTo"), "yyyy-MM-dd"));
			salespricepolicyModel.setPrior(resultSet.getInt("prior"));
			salespricepolicyModel.setCompoundType(resultSet
					.getString("compoundType"));

			// System.out.println(salespricepolicyModel.getCompoundType()+"--"+salespricepolicyModel.getPrior()+"--"+salespricepolicyModel.getSalesPricePolicyCode()+"--"+salespricepolicyModel.getSalesPricePolicyName()+"--"+salespricepolicyModel.getValidDateFrom()+"--"+salespricepolicyModel.getValidDateTo()+"////");
			salespricePolicyList.add(salespricepolicyModel);
		}

		return salespricePolicyList;
	}

}
